package com.epam.mentoring.music.parser;
//import java.sql.Connection.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ChartsSaver {
    protected static String VERSION = "0.1.0";

    public static void main(String[] args) {

        List<DaraSource> dataSourceResults;
        dataSourceResults = getDataSourcesFromDb();
        for (int i = 0; i < dataSourceResults.size(); i++) {
            /* TODO: read XML from web */
            ChartsParser charts = new ChartsParser(dataSourceResults.get(i).getDatasource_url(), dataSourceResults.get(i).getXslt_name());
            List<MusicTrack> tracks;
            tracks = charts.getLatestTracks();

            /* TODO: follow OOP principals */
            /* write to database */
            saveDataToDb(tracks);
        }
    }

    private static List<DaraSource> getDataSourcesFromDb() {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        List<DaraSource> dataSourceResults = new ArrayList<>();

        String url = "jdbc:mysql://mysql2.alwaysdata.com:3306/epamtest_q12014";
        String user = "epamtest";
        String password = "Admin;";

        try {
            con = DriverManager.getConnection(url, user, password);
            st = con.createStatement();
            rs = st.executeQuery("SELECT name, datasource_url, xslt_name FROM datasource");

            while (rs.next()) {
                DaraSource daraSource = new DaraSource(rs.getString(1), rs.getString(2), rs.getString(3));
                dataSourceResults.add(daraSource);
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
        }
        return dataSourceResults;
    }

    public static void saveDataToDb(List<MusicTrack> tracks) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        String url = "jdbc:mysql://mysql2.alwaysdata.com:3306/epamtest_q12014";
        String user = "epamtest";
        String password = "Admin;";

        try {
            con = DriverManager.getConnection(url, user, password);
            st = con.createStatement();
            rs = st.executeQuery("SELECT VERSION()");

            if (rs.next()) {
                System.out.println(rs.getString(1));
            }

            int i = st.executeUpdate("INSERT INTO `supplementary` (software_version, os, username) VALUES ('" + VERSION + "', '" + System.getProperty("os.name") + " - " + System.getProperty("os.version") + " ', '" + System.getProperty("user.name") + "');");

            st.executeUpdate("call add_artist('- default -', '', 'default datasource', '');");
            st.executeUpdate("call add_albums('- Album not found -', '', '', '2000', 'default datasource', '', '- default -');");

            for (MusicTrack track : tracks) {
                System.out.println(track.getName() + track.getArtistName() + track.getAlbumName());
                st.executeUpdate("call add_artist('" + track.getArtistName() + "', '', '', '');");
                st.executeUpdate("call add_albums('" + track.getAlbumName() + "', '', '', '2000', '', '', '" + track.getArtistName() + "');");
                st.executeUpdate("call ins_track('" + track.getName() + "', '" + track.getImageURL() + "', '', '" + track.getArtistName() + "', '" + track.getAlbumName() + "', 'last.fm');");

            }

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
        }
    }
}
